-- 派件及时率明细
insert overwrite table jms_dm.dm_terminal_deliver_punctuality_rate_detail_dt partition (dt)
select billcode -- 运单
       ,network_detal.agent_code as end_agent_code -- 末端代理区code
       ,network_detal.agent_name as end_agent_name  -- 末端代理区name
       ,network_detal.fran_code as end_franchisee_code -- 末端加盟商code
       ,network_detal.fran_name as end_franchisee_name  -- 末端加盟商
       ,send_next_station_code -- 下一站code
       ,send_next_station_name -- 下一站
       ,deliver_network_code  -- 出仓网点code
       ,deliver_network_name  -- 出仓网点
       ,provider_id as end_provider_id  -- 末端省id
       , provider_desc as end_provider_name -- 末端省
       ,network_detal.city_id as end_city_id  -- 末端城市
       ,network_detal.city_desc as end_city_name -- 末端城市
       ,end_center_code  -- 末中心code
       ,end_center_name  -- 末中心
       ,end_center_send_time -- 末端中心发件时间
       ,planned_departure_time -- 规划接件时间
       ,planned_arrival_time   -- 规划到件时间
       ,new_effective_plan_sign_time -- 规划派件时间
       ,span_days_shift              -- 派件跨天
       ,deliver_shift                -- 派件班次
       ,end_network_arrival_time     -- 末端网点到件时间
       ,deliver_time                 -- h出仓时间
       ,case when is_delay_deliver=1 then 1
             when is_arrival_send =1 then 2
              when deliver_network_code is not null then 3
               else 4 end as deliver_type       -- 派件类型 1稍后出仓 2到出一体 3出仓 4其它
       ,order_source_code            -- 订单来源code
       ,order_source_name            -- 订单来源
       ,deliver_township_id  -- 出仓乡镇id
       ,deliver_township_name   -- 出仓乡镇
       ,case when (end_network_source = '共配' or deliver_source = '共配' or sign_source = '共配') then 1 else 0 end is_common -- 是否共配
       ,to_date(new_effective_plan_sign_time) as new_effective_plan_deliver_date -- 规划派件时间
       ,dt as date_time  -- 发件日期
       ,end_network_code   -- 末端到件网点code
       ,end_network_name   -- 末端到件网点
       ,final_sign_network_code as final_deliver_network_code  -- 最终派件网点
       ,final_sign_network_name as final_deliver_network_name  -- 最终派件网点
       ,if(deliver_time is not null and deliver_time<=new_effective_plan_sign_time ,1,0) as is_deliver_punctuality  -- 是否派件准点
       ,if(deliver_time is not null and deliver_time>new_effective_plan_sign_time ,(unix_timestamp(deliver_time)-unix_timestamp(new_effective_plan_sign_time))/60,null) as delay_minutes  -- 延误分数
        ,plan_warehouse_end_time  -- 新规划派件时间
        ,if(deliver_township_id is not null,1,0) as is_township -- 是否乡镇件
        ,special_extra_time  --乡镇加时
        ,final_sign_user_code  --派件员code
       ,final_sign_user_name  --派件员
        ,dt
from jms_dwd.dwd_terminal_sign_all_detail_new_dt t1
left join (select code,city_id,city_desc,provider_id  -- 末端省id
       ,provider_desc -- 末端省
       ,fran_code
       ,fran_name
       ,agent_code
       ,agent_name
    from jms_dim.dim_network_whole_massage
    where is_delete=1
    and is_enable=1
    -- and agent_name not like 'BEST%'
    ) network_detal
on final_sign_network_code=network_detal.code
where dt >= date_sub('{{ execution_date | cst_ds }}',14)
and dt <= '{{ execution_date | cst_ds }}'
and is_reject_reback_transfer = 2
and is_end_piece = 2
and end_center_send_time <= nvl(actual_sign_time,'9999-99-99')
distribute by dt,abs(hash(billcode))%100
-- distribute by dt,pmod(hash(rand()),100)
;